package com.scalar.chiptrack.finance.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.finance.CostCodeInfo;
import com.scalar.chiptrack.finance.forms.CostCodeSetupForm;
import com.scalar.chiptrack.jobs.Job;
import com.scalar.chiptrack.jobs.dao.JobDAO;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.utils.ExcelUtils;
import org.apache.struts.util.LabelValueBean;

/**
 * User: ChandraSekhar
 * Date: May 30, 2007
 * Time: 4:16:06 PM
 */
public final class CostCodeSetupDAO
{
    private static Logger s_logger              = null;
    private static final String LINE_SEPARATOR  = System.getProperty("line.separator");

    public static ArrayList loadCostCodes( DBTransaction dbTransaction ) throws ScalarException
    {
		Connection conn         = null;
		PreparedStatement ps    = null;
		ResultSet rs            = null;
		ArrayList costCodeList  = new ArrayList();
        String costCode         = null;

        try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            ps = conn.prepareStatement( "SELECT DISTINCT COST_CODE FROM COST_CODE_TABLE" );

            rs = ps.executeQuery();

			while ( rs.next() )
			{
                costCode = rs.getString( "COST_CODE" );
                costCodeList.add ( new LabelValueBean( costCode, costCode ) );
            }
        }
        catch ( SQLException se )
		{
		    throw new ScalarException ( "Could not load cost codes", se );
		}
		finally
		{
			if (rs != null)
			{
			    try { rs.close(); } catch (SQLException igonore) { }
			}

			if (ps != null)
			{
			    try { ps.close(); } catch (SQLException igonore) { }
			}

			if (conn != null)
			{
			    try { conn.close(); } catch (SQLException igonore) { }
			}
		}

		return costCodeList;
    }


    public static ArrayList<CostCodeInfo> loadCostCodes ( int year, int  quater ) throws ScalarException
    {
		Connection conn         = null;
		PreparedStatement ps    = null;
		ResultSet rs            = null;
		ArrayList<CostCodeInfo> costCodeList  = new ArrayList<CostCodeInfo>();

		try
		{
			conn = DataSource.getConnection();

            ps = conn.prepareStatement ( "SELECT cc_id, Cost_Code, Die_Cost, Assm_Cost,Test_Cost,Fg_Cost, TR_Cost,\n" +
                    "Wafer_Cost, Assy, Base, Pkg, Pin, pkg_Desc FROM COST_CODE_TABLE WHERE YEAR = ? AND QTR = ? " );
            ps.setInt ( 1, year );
			ps.setInt ( 2, quater );

            rs = ps.executeQuery();

			while ( rs.next() )
			{
                CostCodeInfo costCodeInfo = new CostCodeInfo();
                costCodeInfo.setCostCodeId( rs.getInt("cc_id" ) );
                costCodeInfo.setCostCode( rs.getString("Cost_Code" ) );
                costCodeInfo.setDieCost( rs.getDouble("Die_Cost") );
                costCodeInfo.setAssyCost(rs.getDouble("Assm_Cost") );
                costCodeInfo.setTestCost(rs.getDouble("Test_Cost") );
                costCodeInfo.setFgCost( rs.getDouble("Fg_Cost") );
                costCodeInfo.setTrCost( rs.getDouble("TR_Cost") );
                costCodeInfo.setWaferCost( rs.getDouble("Wafer_Cost") );
                costCodeInfo.setAssy( rs.getString("Assy") );
                costCodeInfo.setBase( rs.getString("Base") );
                costCodeInfo.setPkg( rs.getString("Pkg") );
                costCodeInfo.setPin( rs.getString("Pin") );
                costCodeInfo.setPkgDesc(rs.getString("pkg_Desc") );

                costCodeList.add ( costCodeInfo );
            }
        }
        catch (SQLException se)
		{
		    throw new ScalarException ("Could not load cost codes information for year &Qtr: " + year +" : " +quater, se);
		}

		finally
		{
			if (rs != null)
			{
			    try { rs.close(); } catch (SQLException igonore) { }
			}

			if (ps != null)
			{
			    try { ps.close(); } catch (SQLException igonore) { }
			}

			if (conn != null)
			{
			    try { conn.close(); } catch (SQLException igonore) { }
			}
		}

		return costCodeList;
    }


    public static void saveCostCodeDetails ( DBTransaction dbTransaction, ArrayList<CostCodeInfo> costCodeInfoList , int year, int Qtr ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        boolean success         = false;
        CostCodeInfo costCodeInfo = null;

        if ( costCodeInfoList == null || costCodeInfoList.size() <= 0 )
        {
            return;
        }

        Iterator costCodeIterator = costCodeInfoList.iterator();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall( "{ call sp_save_cost_code_details ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            log( "Saving Cost Codes Information... " );
            while ( costCodeIterator.hasNext() )
            {
                costCodeInfo = ( CostCodeInfo ) costCodeIterator.next();

               // log( costCodeInfo.toString() );

                if ( costCodeInfo.getCostCode() != null && costCodeInfo.getCostCode().trim().length() > 0 )
                {
                    cstmt.setString (1, costCodeInfo.getCostCode());
                    cstmt.setString (2, costCodeInfo.getAssy() );
                    cstmt.setString (3, costCodeInfo.getBase());
                    cstmt.setString (4, costCodeInfo.getPkg());
                    cstmt.setString (5, costCodeInfo.getPin());
                    cstmt.setString (6, costCodeInfo.getPkgDesc());
                    cstmt.setDouble (7, costCodeInfo.getWaferCost());
                    cstmt.setDouble (8, costCodeInfo.getDieCost());
                    cstmt.setDouble (9, costCodeInfo.getAssyCost());
                    cstmt.setDouble (10, costCodeInfo.getTestCost());
                    cstmt.setDouble( 11, costCodeInfo.getTrCost() );
                    cstmt.setDouble( 12, costCodeInfo.getFgCost() );
                    cstmt.setInt( 13, year );
                    cstmt.setInt( 14, Qtr );
                    cstmt.setInt(15, costCodeInfo.getCostCodeId());
                }
                 cstmt.addBatch();
            }
            cstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
       	{
            throw new ScalarException ("Could not save/update CostCodes information", sqle);
        }

		finally
		{
			try
			{
                if ( dbTransaction == null )
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
            }
			catch (SQLException ignore) {}

			if (cstmt != null)
			{
				try { cstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
	}

    public static void saveCostDetails ( DBTransaction dbTransaction, ArrayList<CostCodeInfo> costInfoList ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        CostCodeInfo costCodeInfo = null;

        if ( costInfoList == null || costInfoList.size() <= 0 )
        {
            return;
        }

        Iterator costCodeIterator = costInfoList.iterator();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "DELETE FROM UNIT_COST_HISTORY_TABLE WHERE DATEPART(Q, TIMESTAMP)= DATEPART(Q, CURRENT_TIMESTAMP) AND YEAR(TIMESTAMP) = YEAR(CURRENT_TIMESTAMP) " );
            pstmt.executeUpdate();
            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }

            pstmt = conn.prepareStatement( "DELETE FROM UNIT_COST_TABLE" );
            pstmt.executeUpdate();
            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }

            cstmt = conn.prepareCall( "{ call sp_save_unit_cost_details ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,? ) }" );
            log( "Saving Cost Codes Information... " );
            while ( costCodeIterator.hasNext() )
            {
                costCodeInfo = ( CostCodeInfo ) costCodeIterator.next();

               // log( costCodeInfo.toString() );

                if ( costCodeInfo.getPartNumber() != null && costCodeInfo.getPartNumber().trim().length() > 0 )
                {
                    cstmt.setString (1, costCodeInfo.getPartNumber());
                    cstmt.setString (2, costCodeInfo.getCostAccount());
                    cstmt.setDouble (3, costCodeInfo.getVaDie());
                    cstmt.setDouble (4, costCodeInfo.getVaEProm());
                    cstmt.setDouble (5, costCodeInfo.getVaBG());
                    cstmt.setDouble (6, costCodeInfo.getVaDieFrt());
                    cstmt.setDouble (7, costCodeInfo.getVaAssy());
                    cstmt.setDouble (8, costCodeInfo.getVaAssyFrt());
                    cstmt.setDouble (9, costCodeInfo.getVaTest());
                    cstmt.setDouble (10, costCodeInfo.getVaTR());
                    cstmt.setDouble( 11, costCodeInfo.getStdInAssy() );
                    cstmt.setDouble( 12, costCodeInfo.getStdInTest() );
                    cstmt.setDouble( 13, costCodeInfo.getStdInTR() );
                    cstmt.setDouble( 14, costCodeInfo.getStdOutAssy() );
                    cstmt.setDouble( 15, costCodeInfo.getStdOutTest() );
                    cstmt.setDouble( 16, costCodeInfo.getStdOutTR() );
                    cstmt.setDouble( 17, costCodeInfo.getYieldAssy() );
                    cstmt.setDouble( 18, costCodeInfo.getYieldTest() );
                    cstmt.setDouble( 19, costCodeInfo.getYieldTR() );
                    cstmt.setDouble( 20, costCodeInfo.getStdNextQtr() );
                    cstmt.setDouble( 21, costCodeInfo.getStdNextQtrPlusOne() );

                }
                 cstmt.addBatch();
            }
            cstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
       	{
            throw new ScalarException ("Could not save/update CostCodes information", sqle);
        }

		finally
		{
			try
			{
                if ( dbTransaction == null )
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
            }
			catch (SQLException ignore) {}

			if (cstmt != null)
			{
				try { cstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
	}

    public static ArrayList<CostCodeInfo> loadWaferPartandPkgs (int processStepId, String pkgName, int quarter, int year) throws ScalarException
    {
		Connection conn         = null;
		PreparedStatement ps    = null;
		ResultSet rs            = null;
		ArrayList<CostCodeInfo> costCodeList  = new ArrayList<CostCodeInfo>();

		try
		{
			conn = DataSource.getConnection();

            ps = conn.prepareStatement ( " { call SP_LOAD_WAFER_PART_PKG_DETAILS ( ?, ?, ?, ? ) }" );
            ps.setInt( 1, processStepId );
            ps.setString( 2, pkgName );
            ps.setInt( 3, quarter );
            ps.setInt( 4, year );

            rs = ps.executeQuery();

			while ( rs.next() )
			{
                CostCodeInfo costCodeInfo = new CostCodeInfo();

                costCodeInfo.setFabProcessStepId( rs.getInt("PROCESSSTEP_ID") );
                costCodeInfo.setPartNumber( rs.getString("PART_NUMBER") );
                costCodeInfo.setGdw( rs.getInt("GDW") );
                costCodeInfo.setPkg( rs.getString("PACKAGE_NAME") );
                costCodeInfo.setWaferCost( Double.parseDouble(rs.getString("WaferCost") ) );
                costCodeInfo.setBgCost( Double.parseDouble(rs.getString("BGCost") ) );
                costCodeInfo.setAssyCost( Double.parseDouble(rs.getString("PkgCost") ) );
                costCodeInfo.setTestTimeInSec( Double.parseDouble( rs.getString("testTimeInSec") ) );
                costCodeInfo.setTestCostPerSec( Double.parseDouble(rs.getString("testCostPerSec") ) );
                costCodeInfo.setTrCost( Double.parseDouble(rs.getString("TRCostY") ) );
                costCodeInfo.setTrCostT( Double.parseDouble(rs.getString("TRCostT") ) );
                costCodeInfo.setBulk( Double.parseDouble(rs.getString("bulkCost") ) );
                costCodeInfo.setYieldAssy( Double.parseDouble(rs.getString("YieldAssy") ) );
                costCodeInfo.setYieldTest( Double.parseDouble(rs.getString("YieldTest") ) );
                costCodeInfo.setYield( Double.parseDouble(rs.getString("Yield") ) );

                costCodeInfo.setRaw_wafer_Cost( Double.parseDouble(rs.getString("Raw_Wafer_Cost") ) );
                costCodeInfo.setZro_exp2_Cost( Double.parseDouble(rs.getString("ZRO_EXP2_Cost") ) );
                costCodeInfo.setEpi_vt_Cost( Double.parseDouble(rs.getString("EPI_VT_Cost") ) );
                costCodeInfo.setVn_exp_Cost( Double.parseDouble(rs.getString("VN_EXP_Cost") ) );
                costCodeInfo.setEpi_seal_Cost( Double.parseDouble(rs.getString("Epi_Seal_Cost") ) );
                costCodeInfo.setPre_grind_Cost( Double.parseDouble(rs.getString("Pre_Grind_Cost") ) );
                costCodeInfo.setSort_Cost( Double.parseDouble(rs.getString("Sort_Cost") ) );
                costCodeInfo.setSort_yield( Double.parseDouble(rs.getString("Sort_Yield") ) );
                costCodeInfo.setTotalWaferCost( Double.parseDouble(rs.getString("Total_Wafer_Cost") ) );
                costCodeInfo.setCummCostPerDie( Double.parseDouble(rs.getString("Cumm_Cost_Per_Die") ) );
                costCodeInfo.setBg_yield( Double.parseDouble(rs.getString("Back_Grind_Yield") ) );
                costCodeInfo.setMixmatch_yield( Double.parseDouble(rs.getString("Mix_Match_Yield") ) );
                costCodeInfo.setCmosWaferCost( Double.parseDouble(rs.getString("Cmos_Wafer_Cost") ) );
                costCodeInfo.setMemsWaferCost( Double.parseDouble(rs.getString("MEMS_Wafer_Cost") ) );

                costCodeList.add ( costCodeInfo );
            }
        }
        catch (SQLException se)
		{
		    throw new ScalarException ("Could not load Wafer parts and Pkgs information ", se);
		}

		finally
		{
			if (rs != null)
			{
			    try { rs.close(); } catch (SQLException igonore) { }
			}

			if (ps != null)
			{
			    try { ps.close(); } catch (SQLException igonore) { }
			}

			if (conn != null)
			{
			    try { conn.close(); } catch (SQLException igonore) { }
			}
		}

		return costCodeList;
    }

    public static void saveWaferPartPkgCostDetails ( DBTransaction dbTransaction, ArrayList<CostCodeInfo> costInfoList, String user_id, int quarter, int year ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        CostCodeInfo costCodeInfo = null;

        if ( costInfoList == null || costInfoList.size() <= 0 )
        {
            return;
        }

        Iterator costCodeIterator = costInfoList.iterator();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            /*pstmt = conn.prepareStatement( "DELETE FROM Wafer_Pkg_Cost_History_Table WHERE DATEPART(Q, TIMESTAMP)= DATEPART(Q, CURRENT_TIMESTAMP) AND YEAR(TIMESTAMP) = YEAR(CURRENT_TIMESTAMP) " );
            pstmt.executeUpdate();
            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }

            pstmt = conn.prepareStatement( "DELETE FROM Wafer_Pkg_Cost_Table" );
            pstmt.executeUpdate();
            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }*/

            cstmt = conn.prepareCall( "{ call SP_SAVE_WAFER_PART_PKG_COST_DETAILS ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            
            while ( costCodeIterator.hasNext() )
            {
                costCodeInfo = ( CostCodeInfo ) costCodeIterator.next();
                                
                cstmt.setInt (1, costCodeInfo.getFabProcessStepId());
                cstmt.setString (2, costCodeInfo.getPkg());
                cstmt.setDouble (3, costCodeInfo.getWaferCost());
                cstmt.setDouble (4, costCodeInfo.getBgCost());
                cstmt.setDouble (5, costCodeInfo.getAssyCost());
                cstmt.setDouble (6, costCodeInfo.getTestTimeInSec());
                cstmt.setDouble (7, costCodeInfo.getTestCostPerSec());
                cstmt.setDouble (8, costCodeInfo.getTrCost());
                cstmt.setDouble (9, costCodeInfo.getTrCostT());
                cstmt.setDouble (10, costCodeInfo.getBulk());
                cstmt.setDouble( 11, costCodeInfo.getYieldAssy() );
                cstmt.setDouble( 12, costCodeInfo.getYieldTest() );
                cstmt.setDouble( 13, costCodeInfo.getYield() );
                cstmt.setDouble (14, costCodeInfo.getRaw_wafer_Cost());
                cstmt.setDouble (15, costCodeInfo.getZro_exp2_Cost());
                cstmt.setDouble (16, costCodeInfo.getEpi_vt_Cost());
                cstmt.setDouble (17, costCodeInfo.getVn_exp_Cost());
                cstmt.setDouble (18, costCodeInfo.getEpi_seal_Cost());
                cstmt.setDouble( 19, costCodeInfo.getPre_grind_Cost() );
                cstmt.setDouble( 20, costCodeInfo.getSort_Cost() );
                cstmt.setDouble( 21, costCodeInfo.getSort_yield() );
                cstmt.setDouble( 22, costCodeInfo.getBg_yield() );
                cstmt.setDouble( 23, costCodeInfo.getMixmatch_yield() );
                cstmt.setInt   ( 24, quarter );
                cstmt.setInt   ( 25, year );
                cstmt.setString( 26, user_id );
                cstmt.addBatch();
            }
            cstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle)
       	{
            throw new ScalarException ("Could not save/update Wafer_Pkg_Cost information", sqle);
        }

		finally
		{
			try
			{
                if ( dbTransaction == null )
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
            }
			catch (SQLException ignore) {}

			if (cstmt != null)
			{
				try { cstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
	}

    public static ArrayList getFgStdCostReport ( DBTransaction dbTransaction,  CostCodeSetupForm costCodeReportForm ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList stdCostList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL SP_WAFER_PKG_STANDARD_COST_REPORT ( ?, ? ) }" );

            pstmt.setInt( 1, costCodeReportForm.getYear() );
            pstmt.setInt( 2, costCodeReportForm.getQuater() );
            
            rset    = pstmt.executeQuery();

            stdCostList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get Wafer & Pkg FG Std Cost report deatils", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return stdCostList;
    }
    public static ArrayList getMemsStdCostReport ( DBTransaction dbTransaction,  CostCodeSetupForm costCodeReportForm ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList stdCostList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL SP_WAFER_PKG_MEMS_STANDARD_COST_REPORT ( ?, ? ) }" );

            pstmt.setInt( 1, costCodeReportForm.getYear() );
            pstmt.setInt( 2, costCodeReportForm.getQuater() );

            rset    = pstmt.executeQuery();

            stdCostList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get Wafer & Pkg MEMS Std Cost report deatils", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return stdCostList;
    }
    
    public static ArrayList getCummStdCostReport ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList stdCostList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL SP_CUMMULATIVE_COST_MODEL() }" );

            rset    = pstmt.executeQuery();

            stdCostList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get Wafer & Pkg CMOS Std Cost report deatils", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return stdCostList;
    }
    public static ArrayList getCmosStdCostReport ( DBTransaction dbTransaction,  CostCodeSetupForm costCodeReportForm ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList stdCostList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL SP_WAFER_PKG_CMOS_STANDARD_COST_REPORT ( ?, ? ) }" );

            pstmt.setInt( 1, costCodeReportForm.getYear() );
            pstmt.setInt( 2, costCodeReportForm.getQuater() );

            rset    = pstmt.executeQuery();

            stdCostList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get Wafer & Pkg CMOS Std Cost report deatils", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return stdCostList;
    }
    public static ArrayList getAssyStdCostReport ( DBTransaction dbTransaction,  CostCodeSetupForm costCodeReportForm ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList stdCostList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL SP_WAFER_PKG_ASSY_STANDARD_COST_REPORT ( ?, ? ) }" );

            pstmt.setInt( 1, costCodeReportForm.getYear() );
            pstmt.setInt( 2, costCodeReportForm.getQuater() );

            rset    = pstmt.executeQuery();

            stdCostList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get Wafer & Pkg ASSY Std Cost report deatils", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return stdCostList;
    }
    private static void log (String message)
    {
        if ( message == null) return;

        if (s_logger != null)
        {
            synchronized (s_logger)
            {
                s_logger.logDebug ( message + LINE_SEPARATOR);
            }
        }
        else
        {
            System.out.println (message);
        }
    }

    public static void setLogger (Logger logger)
    {
        s_logger = logger;
    }


	/**
	 * @param costCodeSetupForm
	 */
	public static void saveSchedule(CostCodeSetupForm costCodeSetupForm,String jobName) throws ScalarException
	{
        Job job = new Job();
        String scheduleDays = "";
        String every = "";
        int scheduleTimeInHours = 0;
        int scheduleTimeInMinutes = 0;
        int jobId = JobDAO.fetchJobIdByName( jobName );
        
        if( jobId == -1 )
        {
        	throw new ScalarException( "Could not Fetch the Job for schedule" );
        }
        
        try
        {
            scheduleTimeInHours = Integer.parseInt( costCodeSetupForm.getScheduleTime() );
            scheduleTimeInMinutes = Integer.parseInt( costCodeSetupForm.getScheduleMinute() );
        }
        catch (NumberFormatException nfe)  { }

        Calendar cal = Calendar.getInstance();

        if( scheduleTimeInHours == 12 )
        {
            cal.set( Calendar.HOUR, 0 );
        }
        else
        {
            cal.set( Calendar.HOUR, scheduleTimeInHours );
        }

        cal.set( Calendar.AM_PM, Integer.parseInt( costCodeSetupForm.getAm_pm() ) );
        cal.set( Calendar.MINUTE, scheduleTimeInMinutes);
        cal.set( Calendar.SECOND, 0);
        cal.set( Calendar.MILLISECOND, 0);
        
        Calendar endDateCal = Calendar.getInstance();
        endDateCal.setTime( StringUtils.format( costCodeSetupForm.getScheduleDate() ) );
        endDateCal.add( Calendar.DATE, 1 );
        

        //UPDATE Jobs SET Start_Date = ?, End_Date = ?, Schedule_Time =?, Schedule_Days = ?, Frequency =?, every = ?, occurs = ?, emails=? WHERE Job_ID = ?
        Connection conn 		= 	null;
        PreparedStatement pstmt = null;
        try
		{
        	conn = 	DataSource.getConnection();
        	pstmt = conn.prepareStatement ( SqlQueryMappings.get( "jobs.updateJobScheduleInfo" ) );
        	pstmt.setString 	( 1, StringUtils.formatDate( costCodeSetupForm.getScheduleDate(), "MM/dd/yyyy", "yyyy/MM/dd" )   );
        	// End Date is set to one day from the Current Date
	        pstmt.setString 	( 2, StringUtils.formatDateToString( endDateCal.getTime() ) );
	        pstmt.setTimestamp	( 3, new java.sql.Timestamp ( cal.getTime().getTime() ));
	        pstmt.setString 	( 4, scheduleDays); // need info
	        pstmt.setLong 		( 5, 0 );
	        pstmt.setString		( 6, "1" ); /// Needs info
	        pstmt.setString		( 7, "Daily" ); // Needs info
	        pstmt.setString		( 8, StringUtils.concatStrings( costCodeSetupForm.getEmails(), Mailer.EMAIL_SEPARATOR ) );
	        pstmt.setInt		( 9, jobId );
	
	        pstmt.execute();
		}
        catch(Exception e)
		{
            e.printStackTrace();
            throw new ScalarException ( "could not schedule the job" );
		}
        finally
        {
			try
			{
                if (conn != null)
                {
                    conn.commit();
                }
            }
			catch (SQLException ignore) {}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
	}

    public static ArrayList loadCostCodeUpdateTime( DBTransaction dbTransaction ) throws ScalarException
    {
		Connection conn         = null;
		PreparedStatement ps    = null;
		ResultSet rs            = null;
		ArrayList updateTimeList  = new ArrayList();

        try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            ps = conn.prepareStatement( "select DISTINCT CONVERT(VARCHAR, TIMESTAMP, 101) As Timestamp FROM COST_CODE_History_table order by timestamp DESC" );

            rs = ps.executeQuery();

			while ( rs.next() )
			{
               updateTimeList.add ( new LabelValueBean( rs.getString( "Timestamp" ) , rs.getString( "Timestamp" ) ) );
            }
        }
        catch ( SQLException se )
		{
		    throw new ScalarException ( "Could not load cost codes schedule timestamp", se );
		}
		finally
		{
			if (rs != null)
			{
			    try { rs.close(); } catch (SQLException igonore) { }
			}

			if (ps != null)
			{
			    try { ps.close(); } catch (SQLException igonore) { }
			}

			if (conn != null)
			{
			    try { conn.close(); } catch (SQLException igonore) { }
			}
		}

		return updateTimeList;
    }

    public static void saveUpdateCostDetails ( DBTransaction dbTransaction, CostCodeSetupForm form ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        boolean success         = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall( "{ call SP_UPDATE_COST_FROM_PKG_WAFER_COST_TABLE ( ?, ? ) }" );
            cstmt.setInt( 1, form.getYear() );
            cstmt.setInt( 2, form.getQuater() );

            cstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle)
       	{
            throw new ScalarException ("Could not save/update Cost information", sqle);
        }

		finally
		{
			try
			{
                if ( dbTransaction == null )
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
            }
			catch (SQLException ignore) {}

			if (cstmt != null)
			{
				try { cstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
	}
}
